package com.huaxia.dao.realAct;

import com.huaxia.pojo.realAct.SaleDeptBmAct;

import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/06/08 16:25
 */

public interface SaleDeptBmActMapper {

    @Select("select x.SALEDEPTSNAME,x.saleDeptBmAdvance5000,x.saleDeptBmAdvance7000,x.saleDeptBmAdvance10000,y.saleDeptBmInSure5000,y.saleDeptBmInSure7000,y.saleDeptBmInSure10000 from\n" +
            "            (select sb.SALEDEPTSNAME,NVL(a.a,0)saleDeptBmAdvance5000,NVL(a.b,0)saleDeptBmAdvance7000,NVL(a.c,0)saleDeptBmAdvance10000 from\n" +
            "            (select x.SALEDEPTCODE,SUM(case when x.mys>='5000' then 1 end)a,SUM(case when x.mys>='7000' then 1 end)b,SUM(case when x.mys>='10000' then 1 end)c from\n" +
            "            (select t.SALEDEPTcode, SUM(t.PRESTADPREM)mys from FACT_YX_PREPREM_LIST_day t where\n" +
            "            TO_CHAR(t.RECEIVE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and (IS_IMMEDIATE_TRANSFER = 'Y' or IS_IMMEDIATE_TRANSFER is null) AND t.RNFLAG='N' and (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) AND t.Agentstate='在职' AND t.AGENTGRADE in('BM','SBM','ZG301','ZG302','ZG401')  group by t.agentcode,t.SALEDEPTcode)x group by x.SALEDEPTCODE)a\n" +
            "            right join SD_SALEDEPT sb on sb.SALEDEPTCODE=a.SALEDEPTCODE)x,\n" +
            "\n" +
            "                (select sb.SALEDEPTSNAME,NVL(a.a,0)saleDeptBmInSure5000,NVL(a.b,0)saleDeptBmInSure7000,NVL(a.c,0)saleDeptBmInSure10000 from\n" +
            "            (select x.SALEDEPTCODE,SUM(case when x.YCB>='5000' then 1 end)a,SUM(case when x.YCB>='7000' then 1 end)b,SUM(case when x.YCB>='10000' then 1 end)c FROM\n" +
            "            ( select a.SALEDEPTCODE,NVL(a.cbm,0)-NVL(b.ytm,0) ycb from\n" +
            "            (select t.SALEDEPTCODE, t.AGENTCODE, SUM(WRITTENSTADPREM)cbm from FACT_YX_PREPREM_LIST_day t\n" +
            "            where TO_CHAR(t.ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and t.RNFLAG='N' and t.AGENTSTATE='在职' AND t.AGENTGRADE in('BM','SBM','ZG301','ZG302','ZG401') \n" +
            "            group by t.AGENTCODE,t.SALEDEPTCODE)a left join\n" +
            "            (select s.SALEDEPTCODE, s.AGENTCODE, SUM(WRITTENSTADPREM)ytm from FACT_YX_PREPREM_LIST_day s\n" +
            "            where TO_CHAR(s.YT_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and s.RNFLAG='N' and s.AGENTSTATE='在职' AND s.AGENTGRADE in('BM','SBM','ZG301','ZG302','ZG401') \n" +
            "            group by s.AGENTCODE,s.SALEDEPTCODE)b on a.AGENTCODE=b.AGENTCODE)x GROUP BY x.SALEDEPTCODE)a right join SD_SALEDEPT sb on sb.SALEDEPTCODE=a.SALEDEPTCODE)y\n" +
            "            where x.SALEDEPTSNAME=y.SALEDEPTSNAME order by y.saleDeptBmInSure10000 desc\n")
    List<SaleDeptBmAct> getSaleDeptBmAct();
}
